package com.bothin.experiment4.dao;

import com.bothin.experiment4.dto.StatisticDTO;
import com.bothin.experiment4.model.Customer;
import com.bothin.experiment4.model.Employee;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

public interface CustomerMapper extends Mapper<Customer> {

    @Select("SELECT * FROM `customer`\n" +
            "WHERE customer.customer_name LIKE #{key}")
    public List<Customer> findByKeyword(String key);

    @Select("SELECT customer_name name, SUM(contact.money) value \n" +
            "FROM `contact` LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "WHERE create_time BETWEEN #{start} AND #{end} \n" +
            "GROUP BY customer.customer_id\n" +
            "ORDER BY value DESC\n" +
            "LIMIT 0,#{limit}")
    public List<StatisticDTO> getAllCustomerStatistic1(String start, String end, int limit);

    @Select("SELECT customer_name name, SUM(contact.money) value \n" +
            "FROM `contact` LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "GROUP BY customer.customer_id\n" +
            "ORDER BY value DESC\n" +
            "LIMIT 0,#{limit}")
    public List<StatisticDTO> getAllCustomerStatistic2(int limit);

    @Select("SELECT customer_name name, SUM(contact.money) value \n" +
            "FROM `contact` LEFT JOIN customer on contact.customer_id = customer.customer_id\n" +
            "GROUP BY customer.customer_id\n" +
            "ORDER BY value DESC\n")
    public List<StatisticDTO> getAllCustomerStatistic3();
}